package com.stxx.assistant.framwork.utils;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.stxx.assistant.framwork.constants.LogicType;
import com.stxx.assistant.framwork.domain.model.PageQuery;
import com.stxx.assistant.framwork.domain.model.PageResult;
import com.stxx.assistant.framwork.support.Getter;
import com.stxx.assistant.service.support.BaseService;
import lombok.extern.slf4j.Slf4j;
import org.noear.solon.annotation.Component;
import org.noear.solon.annotation.Init;
import org.noear.solon.annotation.Inject;
import org.noear.solon.data.sql.SqlBuilder;
import org.noear.solon.data.sql.SqlExecutor;
import org.noear.solon.data.sql.SqlUtils;

import java.lang.invoke.SerializedLambda;
import java.lang.reflect.Method;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.concurrent.atomic.AtomicReference;
import java.util.function.Consumer;

/**
 * @author Fuzhenlei
 * @date 2025/01/11 16:33
 */
@Slf4j
@Component
public class StoreUtils {
    static SqlUtils staticSqlUtils;
    static List<Consumer<SqlUtils>> consumers = new ArrayList<>();
    static boolean initialed = false;
    @Inject("config")
    SqlUtils sqlUtils;
    @Init
    public void init() throws SQLException {
        List<String> tables = new ArrayList<>();
        tables.add("CREATE TABLE IF NOT EXISTS sys_config (id TEXT PRIMARY KEY,value TEXT,remark TEXT)");
        tables.add("CREATE TABLE IF NOT EXISTS sys_task (id TEXT PRIMARY KEY,type TEXT,params TEXT,status INTEGER,worker TEXT,create_time INTEGER,exec_time INTEGER,finish_time INTEGER,back_status INTEGER,remark TEXT);");
        tables.add("insert or ignore into sys_config(id,value,remark) values('task-back-url','http://localhost','反馈地址')");
        tables.add("insert or ignore into sys_config(id,value,remark) values('task-file-path','/share/video','文件保存地址')");
        tables.add("insert or ignore into sys_config(id,value,remark) values('ffmpeg-path','D:\\ffmpeg','文件保存地址')");
        tables.forEach(s-> {
            try {
                sqlUtils.sql(s).update();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        });
        staticSqlUtils = sqlUtils;
        if(consumers.size()>0){
            consumers.forEach(consumer->consumer.accept(sqlUtils));
        }
        initialed = true;
    }

    public static void newAfter(Consumer<SqlUtils> consumer){
        if(initialed){
            newAfterRun(consumer);
        }else{
            consumers.add(consumer);
        }
    }
    static void newAfterRun(Consumer<SqlUtils> consumer){

    }

    public static SqlExecutor sql(String sql) {
        return staticSqlUtils.sql(sql);
    }
    public static SqlExecutor sql(String sql,Object...vars) {
        return staticSqlUtils.sql(sql,vars);
    }

    public static SqlExecutor sql(SqlBuilder sql) {
        return staticSqlUtils.sql(sql);
    }

    public static int insert(Object bean) throws SQLException {
        return replaceOrInsert(bean,false);
    }

    private static int replaceOrInsert(Object bean, boolean replace) throws SQLException {
        if(bean==null){
            return 0;
        }
        String table = getTableName(bean.getClass());
        JSONObject jsonObject = JSONUtil.parseObj(bean);
        Object[] params = new Object[jsonObject.size()];
        StringBuilder columnStr = new StringBuilder();
        if(replace){
            columnStr.append("replace into ");
        }else {
            columnStr.append("insert into ");
        }
        columnStr.append(table)
                .append(" (");
        AtomicInteger i = new AtomicInteger(0);
        jsonObject.forEach((key,value) -> {
            columnStr.append( StrUtil.toUnderlineCase(key)).append(",");
            params[i.getAndIncrement()] = value;
        });
        columnStr.setLength(columnStr.length()-1);
        columnStr.append(") values (");
        for (int j = 0; j < params.length; j++) {
            columnStr.append("?").append(",");
        }
        columnStr.setLength(columnStr.length()-1);
        columnStr.append(");");

        return staticSqlUtils.sql(columnStr.toString(),params).update();

    }

    public static int replace(Object bean) throws SQLException {
        return replaceOrInsert(bean,true);
    }

    public static <T> int delete(Class<T> tableClass,Getter<T> getter,Object id) throws SQLException {
        String table = getTableName(tableClass);
        StringBuilder sb = new StringBuilder();
        sb.append("delete from ").append(table).append(" where ").append(columnName(getter)).append(" = ?;");
        return staticSqlUtils.sql(sb.toString(),id).update();
    }

    public static <T> int deleteByIds(Class<T> tableClass,Getter<T> getter,Object[] ids) throws SQLException {
        String table = getTableName(tableClass);
        SqlBuilder sqlSpec = new SqlBuilder()
                .append(String.format("delete from %s where %s in (",table,columnName(getter)));
        for (int i = 0; i < ids.length; i++) {
            sqlSpec.append("?,");
        }
        sqlSpec.removeLast();
        sqlSpec.append(")",ids);
        return staticSqlUtils.sql(sqlSpec).update();
    }

    public static <T> int delete(T bean,Getter<T> ... getters) throws SQLException {
        String table = getTableName(bean.getClass());
        StringBuilder sb = new StringBuilder();
        sb.append("delete from ").append(table).append(" where ");
        Object[] params = new Object[getters.length];
        JSONObject jsonObject = JSONUtil.parseObj(bean);
        for(int i=0;i<getters.length;i++){
            String columnName = columnName(getters[i]);
            sb.append(StrUtil.toUnderlineCase(columnName)).append(" = ? and ");
            params[i]=jsonObject.get(columnName);
        }
        sb.setLength(sb.length()-5);
        return staticSqlUtils.sql(sb.toString(),params).update();
    }

    public static <T> int update(T bean, Getter<T>...fields) throws SQLException {
        String table = getTableName(bean.getClass());
        JSONObject jsonObject = JSONUtil.parseObj(bean);
        SqlBuilder sb = new SqlBuilder();
        sb.append("update ").append(table).append(" set ");
        jsonObject.forEach((key,value) -> {
            sb.append( StrUtil.toUnderlineCase(key)).append(" = ?,",value);
        });
        sb.removeLast();
        sb.append(" where ");
        for (int j = 0; j < fields.length; j++) {
            String fileName = columnName(fields[j]);
            sb.append(StrUtil.toUnderlineCase(fileName)).append(" = ? and ",jsonObject.get(fileName));
        }
        sb.remove(sb.length()-5,5);
        sb.append(";");
        return staticSqlUtils.sql(sb).update();
    }

    public static <T> T selectOne(Class<T> clazz,Getter<T> field,Object id) throws SQLException {
        SqlBuilder sb = new SqlBuilder();
        sb.append("select * from ");
        sb.append(getTableName(clazz));
        sb.append(" where ");
        sb.append(columnName(field));
        sb.append(" = ?;",id);
        return staticSqlUtils.sql(sb).queryRow(clazz);
    }

    public static <T> List<T> selectList(Class<T> clazz) throws SQLException {
        return selectList(clazz,null);
    }

    public static <T> List<T> selectList(Class<T> clazz,T obj) throws SQLException {
        SqlBuilder sb = getListSqlBuilder(clazz,obj);
        sb.insert("select * ");
        return staticSqlUtils.sql(sb).queryRowList(clazz);
    }


    public static <T> PageResult<T> selectPageList(Class<T> clazz, T obj, PageQuery pageQuery) throws SQLException {
        if(pageQuery.getPageNum()==null){
            pageQuery.setPageNum(PageQuery.DEFAULT_PAGE_NUM);
        }
        if(pageQuery.getPageSize()==null){
            pageQuery.setPageSize(PageQuery.DEFAULT_PAGE_SIZE);
        }
        SqlBuilder sb = getListSqlBuilder(clazz,obj);


        sb.backup();
        sb.insert("select * ");
        if(StrUtil.isNotEmpty(pageQuery.getOrderByColumn())){
            if(StrUtil.isBlank(pageQuery.getIsAsc())){
                pageQuery.setIsAsc("asc");
            }
            sb.append(" order by ")
                    .append(pageQuery.getOrderByColumn())
                    .append(" ")
                    .append(pageQuery.getIsAsc());
        }
        sb.append(" limit ?,? ", (pageQuery.getPageNum()-1)*pageQuery.getPageSize(),pageQuery.getPageSize()); //分页获取列表

        List<T> list = staticSqlUtils.sql(sb).queryRowList(clazz);
        //回滚（可以复用备份前的代码构建）
        sb.restore();
        sb.insert("select count(*)  ");
        //查询总数
        Long total = Long.parseLong(staticSqlUtils.sql(sb).queryValue().toString());
        PageResult<T> pageResult = new PageResult(list,total);
        return pageResult;
    }

    static <T> SqlBuilder getListSqlBuilder(Class<T> clazz, T obj){
        String tableName = getTableName(clazz);
        SqlBuilder sb = new SqlBuilder();
        sb.append(" from ").append(tableName);
        if(obj!=null) {
            JSONObject jsonObject = JSONUtil.parseObj(obj);
            AtomicReference<Boolean> isWhere = new AtomicReference<>(false);
            jsonObject.forEach((key, value) -> {
                if (ObjectUtil.isNotEmpty(value)) {
                    String fieldName = StrUtil.toUnderlineCase(key);
                    if (isWhere.get()) {
                        sb.append(" and ");
                    } else {
                        sb.append(" where ");
                        isWhere.set(true);
                    }
                    sb.append(fieldName);
                    LogicType lt = BaseService.getLogic(tableName, fieldName);
                    switch (lt) {
                        case LIKE:
                            sb.append(" like CONCAT('%',?,'%')", value);
                            break;
                        case GT:
                            sb.append(" > ?", value);
                            break;
                        case LT:
                            sb.append(" < ?", value);
                            break;
                        case GE:
                            sb.append(" >= ?", value);
                            break;
                        case LE:
                            sb.append(" <= ?", value);
                            break;
                        case LIKE_LEFT:
                            sb.append(" like CONCAT(?,'%')", value);
                            break;
                        case LIKE_RIGHT:
                            sb.append(" like CONCAT('%',?)", value);
                            break;
                        default:
                            sb.append(" = ?", value);
                            break;
                    }
                }
            });
        }
        return sb;
    }



    public static String getTableName(Class<?> clazz){
        return StrUtil.toUnderlineCase(clazz.getSimpleName());
    }

    private static String columnName(Getter<?> getter){
        try {
            Method method = getter.getClass().getDeclaredMethod("writeReplace");
            method.setAccessible(Boolean.TRUE);
            SerializedLambda lambda = (SerializedLambda) method.invoke(getter);
            return BeanUtil.getFieldName(lambda.getImplMethodName());
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}
